package com.buttonpay;

import java.util.ArrayList;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;
 
public class DataHelper 
{
   private static final String DATABASE_NAME = "ButtonPayDataBase.db";
   private static final int DATABASE_VERSION = 2;
   private static final String TABLE_NAME = "products";
   private static final String TABLE_TOPUP_FAVOURITE="topup_favourite"; 
   private Context context;
   private SQLiteDatabase db; 
   private SQLiteStatement insertStmt,insertStmtFav;
   
   private static final String INSERT = "insert into "
      + TABLE_NAME + "(systemSerID,systemSerName,productID,productName,productDesc,productType,batchID,minVal,maxVal,ImageURL) values (?,?,?,?,?,?,?,?,?,?)";
   
   private static final String INSERT_TOPUP_FAVOURITE = "insert into "
	  + TABLE_TOPUP_FAVOURITE + "(systemSerID,systemSerName,productID,productName,productDesc,productType,batchID,minVal,maxVal,ImageURL) values (?,?,?,?,?,?,?,?,?,?)";
   
   OpenHelper openHelper;
   
   public void updateDB()
   {
	   System.out.println("Updating Databse..");
	   db.delete(TABLE_NAME, null,null);
	   db.delete(TABLE_TOPUP_FAVOURITE, null, null); 
   }
   
   public DataHelper(Context context) 
   {	 
      this.context = context;
      openHelper = new OpenHelper(this.context);
      this.db = openHelper.getWritableDatabase();
      this.insertStmt = this.db.compileStatement(INSERT);
      this.insertStmtFav=this.db.compileStatement(INSERT_TOPUP_FAVOURITE);
   }
    
   
   public long insert_topupFav(String sysSerID,String sysSerName,String productID,String productName,String productDesc,String productType,String batchID,String minVal,String maxVal,String ImageURL) 
   {
	 
	  this.insertStmtFav.bindString(1,sysSerID);
	  this.insertStmtFav.bindString(2, sysSerName);
	  this.insertStmtFav.bindString(3, productID);
	  this.insertStmtFav.bindString(4,productName);
	  this.insertStmtFav.bindString(5, productDesc);
	  this.insertStmtFav.bindString(6,productType);
	  this.insertStmtFav.bindString(7,batchID);
	  this.insertStmtFav.bindString(8,minVal);
	  this.insertStmtFav.bindString(9,maxVal);
	  this.insertStmtFav.bindString(10, ImageURL);
	  System.out.println("Fav item added.."+productName);
	  
	  return this.insertStmtFav.executeInsert();
      
   }
   
   public long insert(String sysSerID,String sysSerName,String productID,String productName,String productDesc,String productType,String batchID,String minVal,String maxVal,String ImageURL) 
   {
	  
	  this.insertStmt.bindString(1,sysSerID);
	  this.insertStmt.bindString(2,sysSerName);
	  this.insertStmt.bindString(3,productID);
	  this.insertStmt.bindString(4,productName);
	  this.insertStmt.bindString(5,productDesc);
	  this.insertStmt.bindString(6,productType);
	  this.insertStmt.bindString(7,batchID);
	  this.insertStmt.bindString(8,minVal);
	  this.insertStmt.bindString(9,maxVal);
	  this.insertStmt.bindString(10,ImageURL);
	  System.out.println("item added..");
	  
	  return this.insertStmt.executeInsert();
      
   }
   
   public void deleteProdctFromFav(String ID) 
   {
		// TODO Auto-generated method stub
		int i=this.db.delete(TABLE_TOPUP_FAVOURITE, "productID"+" =?", new String[] {ID});
		System.out.println(i+ "Row is deleted");
	}

   public void close() 
   {
       if (openHelper != null) 
       {
    	   	openHelper.close();
       }
   }
   
   public void deleteAll()
   {
	  System.out.println("Deleting whole database");
      this.db.delete(TABLE_NAME, null, null);
      this.db.delete(TABLE_TOPUP_FAVOURITE, null, null);
   }
   public int checkProducts()
   {
	   Cursor cursor = this.db.query(TABLE_NAME, new String[] { "systemSerId","systemSerName","productID","productName","productDesc","productType","batchID","minVal","maxVal" }, 
		        null, null, null, null, null);
	   int count=cursor.getCount();
	      if (cursor != null && !cursor.isClosed())
	      {
	         cursor.close();
	      }
	   return count;
	   
   }
   public int checkFav_topup()
   {
	   Cursor cursor = this.db.query(TABLE_TOPUP_FAVOURITE, new String[] { "systemSerId","systemSerName","productID","productName","productDesc","productType","batchID","minVal","maxVal","cursor" }, 
		        null, null, null, null, null);
	   int count=cursor.getCount();
	  
	   if (cursor != null && !cursor.isClosed())
	   {
	       cursor.close();
	   }
	   return count;
   }
   public ArrayList<Products> getFavTopUpProducts(String sysSerID)
   {
	   ArrayList<Products> list = new ArrayList<Products>();
	   Cursor cursor= db.query(TABLE_TOPUP_FAVOURITE,  new String[] { "systemSerId","systemSerName","productID","productName","productDesc","productType","batchID","minVal","maxVal","ImageURL" }
	   ,"systemSerId" +" = ?", new String[]{sysSerID},  null, null, null);
	   	  if(cursor.getCount()>0)
	      {
		      if (cursor.moveToFirst())
		      {
		         do 
		         {	
		        	Products p=new Products(); 
			        p.setSystemServiceID(cursor.getString(0));
			        p.setSystemServiceName(cursor.getString(1));
			        p.setProductID(cursor.getString(2));
			        p.setProductName(cursor.getString(3));
			        p.setProductDesc(cursor.getString(4));
			        p.setProductType(cursor.getString(5));
			        p.setBatchID(cursor.getString(6));
			        p.setMinValue(cursor.getString(7));
			        p.setMaxValue(cursor.getString(8));
			        p.setImageURL(cursor.getString(9));
			        list.add(p);
		        	System.out.println(p.getProductName() +" retrived from database.");
		         }
		         while (cursor.moveToNext());
		      }
		      if(cursor != null && !cursor.isClosed())
		      {
		         cursor.close();
		      }
		      return list;
	      }
	      else
	      {
	    	  System.out.println("No Top Up Products Avilable.");
	    	  return list;
	      }
   }
   
   public ArrayList<Products> getProducts(String sysSerID)
   {
	   System.out.println("System Service ID:"+sysSerID);
	   ArrayList<Products> list = new ArrayList<Products>();
	   Cursor cursor= db.query(TABLE_NAME,  new String[] { "systemSerId","systemSerName","productID","productName","productDesc","productType","batchID","minVal","maxVal","ImageURL" }
	   , "systemSerId" +" = ?", new String[]{sysSerID},  null, null, null);
	   if(cursor.getCount()>0)
	      {
	      
		      if (cursor.moveToFirst())
		      {
		         do 
		         {
		        	    Products p=new Products();
		        	    
			        	p.setSystemServiceID(cursor.getString(0));
			        	p.setSystemServiceName(cursor.getString(1));
			        	p.setProductID(cursor.getString(2));
			        	p.setProductName(cursor.getString(3));
			        	p.setProductDesc(cursor.getString(4));
			        	p.setProductType(cursor.getString(5));
			        	p.setBatchID(cursor.getString(6));
			        	p.setMinValue(cursor.getString(7));
			        	p.setMaxValue(cursor.getString(8));
			            p.setImageURL(cursor.getString(9));
			        	list.add(p);
			        	
			        	System.out.println(p.getProductName() +" retrived from database.");
		         }
		         while (cursor.moveToNext());
		      }
		      if (cursor != null && !cursor.isClosed())
		      {
		         cursor.close();
		      }
		      return list;
	      }
	      else
	      {
	    	  System.out.println("No Top Up Products Avilable");
	    	  return null;
	      }
   }
   
   public boolean isProductInFav(String id, String minVal, String maxVal)
   {
   	// TODO Auto-generated method stub
	   ArrayList<Products> list = new ArrayList<Products>();
	   Cursor cursor= db.query(TABLE_TOPUP_FAVOURITE,  new String[] {"productName"}, "productID = ? and minVal =? and maxVal =?",new String[]{id,minVal,maxVal},  null, null, null);
	   //Cursor cursor= db.query(TABLE_TOPUP_FAVOURITE,  new String[] {"productName"}, new String[] {"productID" +" = ?","minVal"+ " =?","maxVal"+" =?"}, new String[]{id,minVal,maxVal},  null, null, null);
	 
	   if(cursor.getCount()>0)
	      {
	      
		      if (cursor.moveToFirst())
		            	System.out.println(cursor.getString(0) +" is in fav");
			  
		      if (cursor != null && !cursor.isClosed())
		      {
		         cursor.close();
		      }
		      return true;
	      }
	      else
	      {
	    	  System.out.println(id+ "not avilable in fav");
	    	  return false;
	      }
   }
   public ArrayList<Products> selectAll() 
   {
      ArrayList<Products> list = new ArrayList<Products>();
      
      Cursor cursor = this.db.query(TABLE_NAME, new String[] { "systemSerId","systemSerName","productID","productName","productDesc","productType","batchID","minVal","maxVal","ImageURL"}, 
        null, null, null, null, null);
      
      if(cursor.getCount()>0)
      {
      
	      if (cursor.moveToFirst())
	      {
	         do 
	         {
	        	Products p=new Products();
	        	
		        	p.setSystemServiceID(cursor.getString(0));
		        	p.setSystemServiceName(cursor.getString(1));
		        	p.setProductID(cursor.getString(2));
		        	p.setProductName(cursor.getString(3));
		        	p.setProductDesc(cursor.getString(4));
		        	p.setProductType(cursor.getString(5));
		        	p.setBatchID(cursor.getString(6));
		        	p.setMinValue(cursor.getString(7));
		        	p.setMaxValue(cursor.getString(8));
		            p.setImageURL(cursor.getString(9));
		        	list.add(p);
		        
		        	System.out.println(p.getProductName() +" retrived from database.");
	         }
	         while (cursor.moveToNext());
	      }
	      if (cursor != null && !cursor.isClosed())
	      {
	         cursor.close();
	      }
	      return list;
      }
      else
      {
    	  Log.d("No Data","No Products Found in Data base");
    	  return null;
      }
      
   } 
   private static class OpenHelper extends SQLiteOpenHelper 
   {
	  
	  OpenHelper(Context context) 
      {
         super(context, DATABASE_NAME, null, DATABASE_VERSION);
      }
 
      @Override
      public void onCreate(SQLiteDatabase db) 
      { 
    	  db.execSQL("CREATE TABLE " + TABLE_NAME + "(id INTEGER PRIMARY KEY, systemSerID TEXT, systemSerName TEXT, productID TEXT, productName TEXT, productDesc TEXT, productType TEXT, batchID TEXT, minVal TEXT, maxVal TEXT, ImageURL TEXT)");
    	  db.execSQL("CREATE TABLE " + TABLE_TOPUP_FAVOURITE + "(id INTEGER PRIMARY KEY, systemSerID TEXT, systemSerName TEXT, productID TEXT, productName TEXT, productDesc TEXT, productType TEXT, batchID TEXT, minVal TEXT, maxVal TEXT, ImageURL TEXT)");
      }
      
      @Override
      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
      {
         Log.w("Example", "Upgrading database, this will drop tables and recreate.");
         db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
         db.execSQL("DROP TABLE IF EXISTS " + TABLE_TOPUP_FAVOURITE);
         onCreate(db);
      }
      
   }

   
}